from sqlalchemy import create_engine, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

import requests
from lxml import etree
headers = {
    'User-Agent':"Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.84 Safari/537.36"
}

# 初始化数据库连接
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/reptile_data",max_overflow=5)
# 创建对象的基类
Base = declarative_base()

class QuoatCategory(Base):
    __tablename__ = 'quoat_category'
    id = Column(Integer,primary_key=True)
    title = Column(String(255))

class QuoatMenu(Base):
    __tablename__ = 'quoat_menu'
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    href = Column(String(255))
    cate_id = Column(Integer,ForeignKey(QuoatCategory.id))

#定义初始化数据库函数
def init_db():
    Base.metadata.create_all(engine)

'''
将ORM类映射到db数据库中，产生DB表
'''
def create_session():
    init_db()
    Session = sessionmaker(bind=engine)
    session = Session()
    return session


'''
objs-- 传入的ORM类对象或者对象列表
'''
def add_records(session, objs):
    if isinstance(objs, list):
        session.add_all(objs)
        session.commit()
    else:
        session.add(objs)
        session.commit()

'''
查询数据
'''
# def  query_records(session, Cls, Conditions=None):
#     if Conditions == None:
#         return session.query(Cls).all()
#     return session.query(Cls).filter_by(Conditions).all()

if __name__ == "__main__":
    # 生成表
    session = create_session()
    """两者的区别"""
    # a = session.query(QuoatCategory).filter(QuoatCategory.id==1).all()
    # a = session.query(QuoatCategory).filter_by(id=1).all()

    url = "https://www.geyanw.com/"
    response = requests.get(url=url,headers=headers)
    response.encoding = "gbk"
    html_doc = response.text
    html_tree = etree.HTML(html_doc)
    quoat_list = html_tree.xpath('//div[@class="listbox"]/dl')
    for quoat in quoat_list:
        quoat_category_list = quoat.xpath('./dt/strong/a/text()')
        # 保存数据并获取id值
        cate_title = quoat_category_list[0]
        new_catagory = QuoatCategory(title=cate_title)
        add_records(session,new_catagory)
        cate_id = session.query(QuoatCategory).filter(QuoatCategory.title==cate_title).first().id

        quoat_menu_list = quoat.xpath('./dd/ul/li/a/text()')
        quoat_menuhref_list = quoat.xpath('./dd/ul/li/a/@href')

        for i in range(len(quoat_menu_list)):
            title = quoat_menu_list[i]
            href = "https://www.geyanw.com" + quoat_menuhref_list[i]
            new_menu = QuoatMenu(title=title,cate_id=cate_id,href=href)
            add_records(session,new_menu)

    # a = session.query(QuoatMenu).filter(QuoatMenu.cate_id==5).all()
    # for x in a:
    #     print(x.id)

